package bookmanage;

import java.awt.EventQueue;

import javax.swing.JInternalFrame;
import javax.swing.JLabel;
import javax.swing.JComboBox;
import javax.swing.JOptionPane;
import javax.swing.SpringLayout;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.ItemListener;
import java.awt.event.ItemEvent;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;

import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;

import net.sourceforge.jdatepicker.impl.JDatePanelImpl;
import net.sourceforge.jdatepicker.impl.JDatePickerImpl;
import net.sourceforge.jdatepicker.impl.UtilDateModel;

import javax.swing.JButton;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.JTableHeader;

import java.awt.Font;

import javax.swing.JCheckBox;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;

public class BookSED extends JInternalFrame {
	private JLabel lblNewLabel;
	private JComboBox comboBox;
	private JComboBox comboBox1;
	private JScrollPane scrollPane;
	private JTable table;
	private JTextField searchTF;
	private JLabel label;
	private JLabel label_1;
	private JLabel label_2;
	private JLabel label_3;
	private JLabel label_4;
	private JLabel label_5;
	private JTextField b_title;
	private JTextField b_author;
	private JTextField b_publisher;
	private JComboBox code1;
	private JComboBox code2;
	private JTextField b_publishdate;
	private JDatePickerImpl datePicker;
	private SpringLayout springLayout;
	private JButton btnEDI;
	private JButton butDEL;
	private JButton button;

	SimpleDateFormat sd = new SimpleDateFormat("yyyy/MM/dd");
	Connection con;
	DefaultTableModel model = new DefaultTableModel();
	Vector<String> colName = new Vector<String>();
	private JCheckBox selectAll;
	private JLabel label_6;
	private JTextField bookno;

	public BookSED(Connection con) {
		super("도서 조회",true,true,false,true);
		this.con=con;
		colName.add("책 번호");
		colName.add("제목");
		colName.add("저자");
		colName.add("출판사");
		colName.add("출판일");
		colName.add("대분류");
		colName.add("소분류");
		setResizable(false);
		setBounds(100, 100, 700, 350);
		getContentPane().setLayout(null);
		getContentPane().add(getLblNewLabel());
		getContentPane().add(getComboBox());
		getContentPane().add(getScrollPane());
		getContentPane().add(getSearchTF());
		getContentPane().add(getLabel());
		getContentPane().add(getLabel_1());
		getContentPane().add(getLabel_2());
		getContentPane().add(getLabel_3());
		getContentPane().add(getLabel_4());
		getContentPane().add(getLabel_5());
		getContentPane().add(getB_title());
		getContentPane().add(getB_author());
		getContentPane().add(getB_publisher());
		getContentPane().add(getCode2());
		getContentPane().add(getCode1());
		getContentPane().add(getB_publishdate());
		getContentPane().add(getDatePicker());
		getContentPane().add(getBtnEDI());
		getContentPane().add(getButDEL());
		getContentPane().add(getButton());
		getContentPane().add(getSelectAll());
		getContentPane().add(getLabel_6());
		getContentPane().add(getBookno());
		datePicker.setVisible(true);
		JTableHeader jh = table.getTableHeader();
		jh.setReorderingAllowed(false);
	}

	private JLabel getLblNewLabel() {
		if (lblNewLabel == null) {
			lblNewLabel = new JLabel("검색 조건");
			lblNewLabel.setBounds(12, 10, 57, 15);
		}
		return lblNewLabel;
	}
	private JComboBox getComboBox() {
		if (comboBox == null) {
			comboBox = new JComboBox();
			comboBox.addItem("제목");
			comboBox.addItem("저자");
			comboBox.addItem("출판사");
			comboBox.addItem("대분류");
			comboBox.addItem("소분류");
			comboBox.addItemListener(new ItemListener() {
				public void itemStateChanged(ItemEvent e) {
					String str = (String)comboBox.getSelectedItem();
					if(comboBox1 != null) getContentPane().remove(comboBox1);
					if(searchTF != null) getContentPane().remove(searchTF);
					if(str.equals("대분류") || str.equals("소분류")){
						comboBox1 = new JComboBox();
						comboBox1.setBounds(170, 7, 120, 24);
						getContentPane().add(comboBox1);
						if(str.equals("대분류")) selectBig();
						else if(str.equals("소분류")) selectSmall();

					}else{
						searchTF = new JTextField();
						searchTF.setBounds(170, 7, 120, 24);
						searchTF.setColumns(10);
						getContentPane().add(searchTF);
					}
					BookSED.this.updateUI();
				}
			});
			comboBox.setBounds(81, 7, 70, 22);
		}
		return comboBox;
	}
	private JScrollPane getScrollPane() {
		if (scrollPane == null) {
			scrollPane = new JScrollPane();
			scrollPane.setBounds(12, 44, 440, 217);
			scrollPane.setViewportView(getTable());
		}
		return scrollPane;
	}
	private JTable getTable() {
		if (table == null) {
			model = new DefaultTableModel(colName, 0) {
				// 셀 내용 수정 불가
				public boolean isCellEditable(int rowIndex, int mColIndex) {
					return false;
				}
			};
			table = new JTable(model);
			table.addMouseListener(new MouseAdapter() {
				@Override
				public void mouseReleased(MouseEvent e) {
					int idxrow = table.getSelectedRow();
					bookno.setText(Integer.toString((Integer)table.getValueAt(idxrow, 0)));
					b_title.setText((String)table.getValueAt(idxrow, 1));
					b_author.setText((String)table.getValueAt(idxrow, 2));
					b_publisher.setText((String)table.getValueAt(idxrow, 3));
					b_publishdate.setText((String)table.getValueAt(idxrow, 4));
					String big = (String)table.getValueAt(idxrow, 5);
					String small = (String)table.getValueAt(idxrow, 6);
					if(small==null) small="선택"; 
					code1.setSelectedItem(big);
					code2.setSelectedItem(small);

				}
			});
		}
		return table;
	}
	private JTextField getSearchTF() {
		if (searchTF == null) {
			searchTF = new JTextField();
			searchTF.setBounds(170, 7, 120, 24);
			searchTF.setColumns(10);
		}
		return searchTF;
	}
	private JLabel getLabel() {
		if (label == null) {
			label = new JLabel("제목");
			label.setBounds(470, 95, 57, 15);
		}
		return label;
	}
	private JLabel getLabel_1() {
		if (label_1 == null) {
			label_1 = new JLabel("저자");
			label_1.setBounds(470, 130, 57, 15);
		}
		return label_1;
	}
	private JLabel getLabel_2() {
		if (label_2 == null) {
			label_2 = new JLabel("출판사");
			label_2.setBounds(470, 165, 57, 15);
		}
		return label_2;
	}
	private JLabel getLabel_3() {
		if (label_3 == null) {
			label_3 = new JLabel("출판일");
			label_3.setBounds(470, 200, 57, 15);
		}
		return label_3;
	}
	private JLabel getLabel_4() {
		if (label_4 == null) {
			label_4 = new JLabel("대분류");
			label_4.setBounds(470, 235, 57, 15);
		}
		return label_4;
	}
	private JLabel getLabel_5() {
		if (label_5 == null) {
			label_5 = new JLabel("소분류");
			label_5.setBounds(470, 270, 57, 15);
		}
		return label_5;
	}
	private JTextField getB_title() {
		if (b_title == null) {
			b_title = new JTextField();
			b_title.setBounds(514, 90, 150, 24);
			b_title.setColumns(10);
		}
		return b_title;
	}
	private JTextField getB_author() {
		if (b_author == null) {
			b_author = new JTextField();
			b_author.setColumns(10);
			b_author.setBounds(514, 125, 150, 24);
		}
		return b_author;
	}
	private JTextField getB_publisher() {
		if (b_publisher == null) {
			b_publisher = new JTextField();
			b_publisher.setColumns(10);
			b_publisher.setBounds(514, 160, 150, 24);
		}
		return b_publisher;
	}
	private JComboBox getCode1() {
		if (code1 == null) {
			code1 = new JComboBox();
			code1.addItemListener(new ItemListener() {
				public void itemStateChanged(ItemEvent e) {
					selectSmall1();
				}
			});
			code1.addItem("선택");
			selectBig1();
			code1.setBounds(514, 230, 115, 23);
		}
		return code1;
	}
	private JComboBox getCode2() {
		if (code2 == null) {
			code2 = new JComboBox();
			code2.addItem("선택");
			code2.setBounds(514, 265, 115, 23);
		}
		return code2;
	}
	private JTextField getB_publishdate() {
		if (b_publishdate == null) {
			b_publishdate = new JTextField();
			b_publishdate.setEditable(false);
			b_publishdate.setColumns(10);
			b_publishdate.setBounds(514, 195, 100, 24);
		}
		return b_publishdate;
	}
	private JDatePickerImpl getDatePicker(){
		if (datePicker == null) {
			UtilDateModel uModel = new UtilDateModel();
			JDatePanelImpl datePanel = new JDatePanelImpl(uModel);
			datePicker = new JDatePickerImpl(datePanel);
			datePicker.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					b_publishdate.setText(sd.format((Date)datePicker.getModel().getValue()));
				}
			});
			datePicker.setBorder(null);
			springLayout = (SpringLayout) datePicker.getLayout();
			//datePicker.setBackground(Color.WHITE);
			datePicker.getJFormattedTextField().setVisible(false);
			datePicker.setSize(45, 27);
			datePicker.setLocation(616, 195);

		}
		return datePicker;
	}
	private JButton getBtnEDI() {
		if (btnEDI == null) {
			btnEDI = new JButton("수정");
			btnEDI.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					String str = bookno.getText().trim();
					if(str.equals("")){
						JOptionPane.showMessageDialog(null, "도서정보가 없습니다");
						return;
					}
					int b_no = Integer.parseInt(str);
					String title = b_title.getText().trim();
					String author = b_author.getText().trim();
					String publisher = b_publisher.getText().trim();
					String publishdate = b_publishdate.getText().trim();
					String big = (String)code1.getSelectedItem();
					String small = (String)code2.getSelectedItem();

					if(big.equals("선택")) big="";
					if(small.equals("선택")) small="";

					if(title.equals("")){
						JOptionPane.showMessageDialog(null, "제목을 입력해주세요!");
						b_title.requestFocus();
						return;
					}
					if(author.equals("")){
						JOptionPane.showMessageDialog(null, "저자를 입력해주세요!");
						b_author.requestFocus();
						return;
					}
					if(publisher.equals("")){
						JOptionPane.showMessageDialog(null, "출판사를 입력해주세요!");
						b_publisher.requestFocus();
						return;
					}
					if(big.equals("")){
						JOptionPane.showMessageDialog(null, "대분류를 선택 해주세요!");
						return;
					}
					int yn = JOptionPane.showConfirmDialog(null, "정말 수정하시겠습니까?",
							"확인창",JOptionPane.YES_NO_OPTION);
					if(yn==JOptionPane.YES_OPTION){
						try {
							String sql="update bookinfo set b_title=?,b_author=?,b_publisher=?,b_publishdate=?,"
									+ "big_name=?,small_name=? where book_no=?";
							PreparedStatement psmt = con.prepareStatement(sql);
							psmt.setString(1, title);
							psmt.setString(2, author);
							psmt.setString(3, publisher);
							psmt.setString(4, publishdate);
							psmt.setString(5, big);
							psmt.setString(6, small);
							psmt.setInt(7, b_no);

							int cnt = psmt.executeUpdate();
							if(cnt==1){
								JOptionPane.showMessageDialog(null, "수정되었습니다!");
								select();
							}
						} catch (Exception e2) {
							e2.printStackTrace();
						}
					}
				}
			});
			btnEDI.setFont(new Font("굴림", Font.BOLD, 15));
			btnEDI.setBounds(459, 5, 95, 25);
		}
		return btnEDI;
	}
	private JButton getButDEL() {
		if (butDEL == null) {
			butDEL = new JButton("삭제");
			butDEL.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					try {
						String str = bookno.getText().trim();
						int yn = JOptionPane.showConfirmDialog(null, "정말 삭제 하시겠습니까?",
								"확인창",JOptionPane.YES_NO_OPTION);
						if(yn==JOptionPane.YES_OPTION){
							if(str.equals("")){
								JOptionPane.showMessageDialog(null, "도서정보가 없습니다");
								return;
							}
							int b_no = Integer.parseInt(str);
							String sql="delete from bookinfo where b_no=?";
							PreparedStatement psmt = con.prepareStatement(sql);
							psmt.setInt(1, b_no);
							int cnt = psmt.executeUpdate();
							if(cnt==1){
								JOptionPane.showMessageDialog(null, "삭제 되었습니다");
								bookno.setText("");
								b_title.setText("");
								b_author.setText("");
								b_publisher.setText("");
								b_publishdate.setText("");
								code1.setSelectedIndex(0);
								code2.setSelectedIndex(0);
								select();
							}
						}
					} catch (Exception e2) {
						e2.printStackTrace();
					}
				}
			});
			butDEL.setFont(new Font("굴림", Font.BOLD, 15));
			butDEL.setBounds(566, 5, 95, 25);
		}
		return butDEL;
	}
	private JButton getButton() {
		if (button == null) {
			button = new JButton("검색");
			button.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					select();
				}
			});
			button.setFont(new Font("굴림", Font.BOLD, 15));
			button.setBounds(357, 5, 95, 25);
		}
		return button;
	}
	private JCheckBox getSelectAll() {
		if (selectAll == null) {
			selectAll = new JCheckBox("전체");
			selectAll.setBounds(298, 6, 57, 22);
		}
		return selectAll;
	}

	public void selectBig(){
		try {
			comboBox1.removeAllItems();
			String sql="select big_name from codetbl where big_name!='강좌' and small_name is null";
			PreparedStatement psmt = con.prepareStatement(sql);
			ResultSet rs = psmt.executeQuery();
			while(rs.next()){
				comboBox1.addItem(rs.getString(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public void selectSmall(){
		try {
			comboBox1.removeAllItems();
			String sql="select small_name from codetbl where big_name!='강좌' and small_name is not null";
			PreparedStatement psmt = con.prepareStatement(sql);
			ResultSet rs = psmt.executeQuery();
			while(rs.next()){
				comboBox1.addItem(rs.getString(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	private JLabel getLabel_6() {
		if (label_6 == null) {
			label_6 = new JLabel("책 번호");
			label_6.setBounds(470, 60, 57, 15);
		}
		return label_6;
	}
	private JTextField getBookno() {
		if (bookno == null) {
			bookno = new JTextField();
			bookno.setEditable(false);
			bookno.setColumns(10);
			bookno.setBounds(514, 55, 150, 24);
		}
		return bookno;
	}

	public void selectBig1(){
		try {
			String sql="select big_name from codetbl where big_name!='강좌' and small_name is null";
			PreparedStatement psmt = con.prepareStatement(sql);
			ResultSet rs = psmt.executeQuery();
			while(rs.next()){
				code1.addItem(rs.getString(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public void selectSmall1(){
		try {
			String big = (String)code1.getSelectedItem();
			code2.removeAllItems();
			code2.addItem("선택");
			String sql="select small_name from codetbl where big_name=? and small_name is not null";
			PreparedStatement psmt = con.prepareStatement(sql);
			psmt.setString(1, big);
			ResultSet rs = psmt.executeQuery();
			while(rs.next()){
				code2.addItem(rs.getString(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void select(){
		try {
			int cb = comboBox.getSelectedIndex();
			String str="";
			String sql="";
			PreparedStatement psmt;
			ResultSet rs;
			if(searchTF != null) str = searchTF.getText().trim();
			if(comboBox1 != null) str = (String)comboBox1.getSelectedItem();
			if(selectAll.isSelected()==true){
				model.setRowCount(0);
				sql="select * from bookinfo";
				psmt = con.prepareStatement(sql);
				rs = psmt.executeQuery();
				while(rs.next()){
					Vector vec = new Vector();
					vec.add(rs.getInt(1));
					vec.add(rs.getString(2));
					vec.add(rs.getString(3));
					vec.add(rs.getString(4));
					Date d = rs.getDate(5);
					if(d != null) vec.add(sd.format(d));
					else vec.add("");
					vec.add(rs.getString(6));
					vec.add(rs.getString(7));
					model.addRow(vec);
				}
			}else{
				model.setRowCount(0);
				if(str.equals("")){
					JOptionPane.showMessageDialog(null, "검색어를 입력, 선택 해주세요!");
					return;
				}
				if(cb==0) str="where b_title like '%"+str+"%'";
				else if(cb==1) str="where b_author like '%"+str+"%'";
				else if(cb==2) str="where b_publisher like '%"+str+"%'";
				else if(cb==3) str="where big_name='"+str+"'";
				else if(cb==4) str="where small_name='"+str+"'";
				sql="select * from bookinfo "+str;
				psmt = con.prepareStatement(sql);
				rs = psmt.executeQuery();
				while(rs.next()){
					Vector vec = new Vector();
					vec.add(rs.getInt(1));
					vec.add(rs.getString(2));
					vec.add(rs.getString(3));
					vec.add(rs.getString(4));
					Date d = rs.getDate(5);
					if(d != null) vec.add(sd.format(d));
					else vec.add("");
					vec.add(rs.getString(6));
					vec.add(rs.getString(7));
					model.addRow(vec);
				}
			}

		} catch (Exception e2) {
			e2.printStackTrace();
		}
	}
	
}
